package org.jeecg.modules.oms.parser.impl.optimization;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.modules.oms.entity.ProductSalesOrder;
import org.jeecg.modules.oms.entity.ProductSalesOrderProgress;
import org.jeecg.modules.oms.entity.XingeProfileCutDetail;
import org.jeecg.modules.oms.entity.XingeProfileMaterial;
import org.jeecg.modules.oms.parser.OptimizationFileProcessor;
import org.jeecg.modules.oms.service.IDesignFileService;
import org.jeecg.modules.oms.service.IProductSalesOrderProgressService;
import org.jeecg.modules.oms.service.IProductSalesOrderService;
import org.jeecg.modules.oms.service.IXingeProfileCutDetailService;
import org.jeecg.modules.oms.service.IXingeProfileMaterialService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.concurrent.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 新格尔优化单处理器
 */
@Slf4j
@Component
public class XingeOptimizationFileProcessor implements OptimizationFileProcessor {

    @Autowired
    private IProductSalesOrderService productSalesOrderService;
    
    @Autowired
    private IXingeProfileMaterialService xingeProfileMaterialService;
    
    @Autowired
    private IXingeProfileCutDetailService xingeProfileCutDetailService;
    
    @Autowired
    private IProductSalesOrderProgressService productSalesOrderProgressService;
    
    @Autowired
    private IDesignFileService designFileService;
    
    /**
     * 线程池，用于并发处理Excel的不同sheet
     */
    private static final ExecutorService executorService = new ThreadPoolExecutor(
        5,
        10,
        60,
        TimeUnit.SECONDS,
        new LinkedBlockingQueue<>(100),
        Executors.defaultThreadFactory(),
        new ThreadPoolExecutor.CallerRunsPolicy()
    );

    /**
     * 处理新格尔优化单文件
     * @param orderId 订单ID  多个订单
     * @param file 上传的文件
     * @param userId 用户ID
     * @param userName 用户名
     * @param orderList
     * @return
     */
    @Override
    public boolean processOptimizationFile(String orderId, MultipartFile file, String userId, String userName,List<ProductSalesOrder> orderList) {
        log.info("开始处理新格尔优化单文件, 订单ID: {}", orderId);
        
        try {
            // 获取订单信息  修改为根据orderId获取订单列表  并拼接orderNo
            String[] orderIdArray = orderId.split(",");
            ArrayList<String> orderNoList = new ArrayList<>();
            for (ProductSalesOrder order : orderList){
                if (order == null) {
                    log.error("未找到订单, 订单ID: {}", orderId);
                    return false;
                }else {
                    orderNoList.add(order.getOrderNo());
                }
            }
            
            // 读取Excel文件
            Workbook workbook = getWorkbook(file);
            if (workbook == null) {
                return false;
            }
            
            // 解析型材清单
            List<XingeProfileMaterial> profileMaterials = parseProfileMaterials(workbook, orderId, String.join(",", orderNoList));
            
            // 保存型材清单
            if (!profileMaterials.isEmpty()) {
                // 先删除旧数据
                xingeProfileMaterialService.deleteByOrderId(orderId);
                
                // 批量保存新数据
                boolean saved = xingeProfileMaterialService.saveBatch(profileMaterials);
                if (!saved) {
                    log.error("保存型材清单失败, 订单ID: {}", orderId);
                    return false;
                }
            } else {
                log.warn("未解析到型材清单数据, 订单ID: {}", orderId);
            }
            
            // 解析详细切割方案 orderNoList拼接为字符串 逗号分割
            List<XingeProfileCutDetail> cutDetails = parseDetailMaterials(workbook, orderId, String.join(",", orderNoList));
            
            // 保存详细切割方案
            if (!cutDetails.isEmpty()) {
                // 先删除旧数据
                xingeProfileCutDetailService.deleteByOrderId(orderId);
                
                // 批量保存新数据
                boolean saved = xingeProfileCutDetailService.saveBatch(cutDetails);
                if (!saved) {
                    log.error("保存详细切割方案失败, 订单ID: {}", orderId);
                    return false;
                }
                
                log.info("新格尔优化单详细切割方案处理成功, 订单ID: {}, 切割方案数量: {}", orderId, cutDetails.size());
            } else {
                log.warn("未解析到详细切割方案数据, 订单ID: {}", orderId);
            }
            
            // 调用designFileService保存优化单文件
            try {
                boolean fileSuccess = designFileService.uploadOptimizationFile(orderId, file, userId, userName);
                if (!fileSuccess) {
                    log.warn("保存优化单文件记录失败, 订单ID: {}", orderId);
                    // 不影响主流程，继续执行
                }
            } catch (Exception e) {
                log.error("保存优化单文件记录异常, 订单ID: {}", orderId, e);
                // 不影响主流程，继续执行
            }
            
            // 更新订单状态为"已上传优化单-待提交审核"(状态码3)
            for (String id : orderIdArray) {
                ProductSalesOrder order = productSalesOrderService.getById(id);
                Integer oldStatus = order.getStatus();
                if (oldStatus != null) { // 确保当前状态为"已上传汇总单-待上传优化单"
                    order.setStatus(3); // 更新为"已上传优化单-待提交审核"
                    productSalesOrderService.updateById(order);

                    // 记录订单进度
                    try {
                        // 创建订单进度记录
                        ProductSalesOrderProgress progress = new ProductSalesOrderProgress();
                        progress.setOrderId(id);
                        progress.setOldStatus(oldStatus);
                        progress.setNewStatus(3);
                        progress.setChangeTime(new Date());
                        progress.setOperatorId(userId);
                        progress.setOperatorName(userName);
                        progress.setChangeReason("上传优化单");
                        productSalesOrderProgressService.save(progress);
                    } catch (Exception e) {
                        // 进度记录失败不影响主流程
                        log.error("记录订单进度失败", e);
                    }
                }
            }

            
            log.info("新格尔优化单文件处理成功, 订单ID: {}, 型材数量: {}", orderId, profileMaterials.size());
            return true;
        } catch (Exception e) {
            log.error("处理新格尔优化单文件失败", e);
            return false;
        }
    }
    
    /**
     * 获取Workbook对象
     */
    private Workbook getWorkbook(MultipartFile file) {
        try {
            InputStream inputStream = file.getInputStream();
            return new XSSFWorkbook(inputStream);
        } catch (IOException e) {
            log.error("读取Excel文件失败", e);
            return null;
        }
    }
    
    /**
     * 从Excel中解析订单编号
     */
    private String parseOrderNo(Workbook workbook) {
        Sheet sheet = workbook.getSheetAt(0); // 获取第一个Sheet
        
        for (int rowNum = 0; rowNum <= 10; rowNum++) { // 只检查前10行
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                continue;
            }
            
            for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {
                Cell cell = row.getCell(colNum);
                if (cell != null && cell.getCellType() == CellType.STRING) {
                    String cellValue = cell.getStringCellValue();
                    if (cellValue.contains("客户:") && colNum > 0) {
                        // 订单编号通常在"客户:"所在行的第一列
                        Cell orderNoCell = row.getCell(0);
                        if (orderNoCell != null) {
                            return getCellValueAsString(orderNoCell).trim();
                        }
                    }
                }
            }
        }
        
        return null;
    }
    
    /**
     * 解析型材清单
     */
    private List<XingeProfileMaterial> parseProfileMaterials(Workbook workbook, String orderId, String orderNo) {
        Sheet sheet = workbook.getSheetAt(0);
        List<XingeProfileMaterial> sheetMaterials = parseSheetProfileMaterials(sheet, orderId, orderNo);
        return sheetMaterials;
    }
    
    /**
     * 解析单个Sheet中的型材清单
     */
    private List<XingeProfileMaterial> parseSheetProfileMaterials(Sheet sheet, String orderId, String orderNo) {
        List<XingeProfileMaterial> materials = new ArrayList<>();
        
        // 找到型材清单的表头行
        int headerRowNum = findHeaderRow(sheet);
        if (headerRowNum == -1) {
            log.info("在Sheet[{}]中未找到型材清单表头", sheet.getSheetName());
            return materials;
        }
        
        // 解析列索引
        Map<String, Integer> columnIndexMap = parseColumnIndexes(sheet.getRow(headerRowNum));
        if (!columnIndexMap.containsKey("编号名称颜色") || 
            !columnIndexMap.containsKey("长度") || 
            !columnIndexMap.containsKey("根数")) {
            log.warn("在Sheet[{}]中找到的表头不完整, 缺少必要列", sheet.getSheetName());
            return materials;
        }
        
        // 逐行解析数据
        int currentRow = headerRowNum + 1;
        Row row;
        while ((row = sheet.getRow(currentRow)) != null) {
            // 检查是否到达清单结束位置
            Cell firstCell = row.getCell(0);
            if (firstCell == null || isEmptyRow(row) || 
                (firstCell.getCellType() == CellType.STRING && 
                 (firstCell.getStringCellValue().trim().contains("名称:") || 
                  firstCell.getStringCellValue().trim().contains("米重:")))) {
                break;
            }
            
            // 获取编号/名称/颜色列的值
            Cell codeNameColorCell = row.getCell(columnIndexMap.get("编号名称颜色"));
            if (codeNameColorCell == null) {
                currentRow++;
                continue;
            }
            
            String codeNameColor = getCellValueAsString(codeNameColorCell).trim();
            if (StringUtils.isBlank(codeNameColor)) {
                currentRow++;
                continue;
            }
            
            // 解析编号、名称和颜色
            String code = "";
            String name = "";
            String color = "";
            
            // 使用正则表达式解析"YC5501D / 边框 / 木纹/灰色"这样的格式
            Pattern pattern = Pattern.compile("([^/]+)/\\s*([^/]+)(?:/\\s*(.+))?");
            Matcher matcher = pattern.matcher(codeNameColor);
            if (matcher.find()) {
                code = matcher.group(1).trim();
                name = matcher.group(2).trim();
                color = matcher.groupCount() > 2 && matcher.group(3) != null ? matcher.group(3).trim() : "";
            } else {
                // 如果正则匹配失败，尝试简单分割
                String[] parts = codeNameColor.split("/");
                if (parts.length > 0) code = parts[0].trim();
                if (parts.length > 1) name = parts[1].trim();
                if (parts.length > 2) color = parts[2].trim();
            }
            
            // 获取长度、根数、重量和备注
            Double length = getCellValueAsDouble(row, columnIndexMap.get("长度"));
            Double quantity = getCellValueAsDouble(row, columnIndexMap.get("根数"));
            Double weight = getCellValueAsDouble(row, columnIndexMap.get("重量"));
            String remark = columnIndexMap.containsKey("备注") ? 
                getCellValueAsString(row.getCell(columnIndexMap.get("备注"))).trim() : "";
            
            // 创建型材对象并添加到列表
            XingeProfileMaterial material = new XingeProfileMaterial();
            material.setOrderId(orderId);
            material.setOrderNo(orderNo);
            material.setProfileCode(code);
            material.setProfileName(name);
            material.setColor(color != null ? color : "");
            material.setLength(length);
            material.setQuantity(quantity != null ? quantity.intValue() : 0);
            material.setWeight(weight);
            material.setRemark(remark);
            material.setCreateTime(new Date());
            
            materials.add(material);
            currentRow++;
        }
        
        return materials;
    }
    
    /**
     * 在Sheet中查找型材清单的表头行
     */
    private int findHeaderRow(Sheet sheet) {
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;
            Cell cell = row.getCell(0);
            if(cell == null) continue;
            String cellValue = cell.getStringCellValue().trim();
            // 检查是否包含型材清单的表头列
            if (cellValue.contains("编号") && cellValue.contains("名称") && cellValue.contains("颜色")) {
                return rowNum;
            }
        }
        
        return -1;
    }
    
    /**
     * 解析表头列的索引
     */
    private Map<String, Integer> parseColumnIndexes(Row headerRow) {
        Map<String, Integer> columnMap = new HashMap<>();
        
        for (int colNum = 0; colNum < headerRow.getLastCellNum(); colNum++) {
            Cell cell = headerRow.getCell(colNum);
            if (cell == null) continue;
            
            String cellValue = getCellValueAsString(cell).trim();
            
            // 根据列标题确定列类型
            if (cellValue.contains("编号") && cellValue.contains("名称") && cellValue.contains("颜色")) {
                columnMap.put("编号名称颜色", colNum);
            } else if (cellValue.equalsIgnoreCase("长度")) {
                columnMap.put("长度", colNum);
            } else if (cellValue.equalsIgnoreCase("根数")) {
                columnMap.put("根数", colNum);
            } else if (cellValue.equalsIgnoreCase("重量")) {
                columnMap.put("重量", colNum);
            } else if (cellValue.equalsIgnoreCase("备注")) {
                columnMap.put("备注", colNum);
            }
        }
        
        return columnMap;
    }
    
    /**
     * 获取单元格的Double值
     */
    private Double getCellValueAsDouble(Row row, Integer colIndex) {
        if (colIndex == null || row == null) return null;
        
        Cell cell = row.getCell(colIndex);
        if (cell == null) return null;
        
        switch (cell.getCellType()) {
            case NUMERIC:
                return cell.getNumericCellValue();
            case STRING:
                try {
                    return Double.parseDouble(cell.getStringCellValue().trim());
                } catch (NumberFormatException e) {
                    return null;
                }
            default:
                return null;
        }
    }

    /**
     * 获取单元格的字符串值
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 处理数字格式的单元格，避免科学计数法
                    double value = cell.getNumericCellValue();
                    if (value == Math.floor(value)) {
                        return String.valueOf((long) value);
                    } else {
                        return String.valueOf(value);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    try {
                        return cell.getStringCellValue();
                    } catch (Exception ex) {
                        return "";
                    }
                }
            default:
                return "";
        }
    }

    private boolean isEmptyRow(Row row) {
        if (row == null) {
            return true;
        }
        
        for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {
            Cell cell = row.getCell(colNum);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                // 如果是字符串类型，检查内容是否为空
                if (cell.getCellType() == CellType.STRING) {
                    String value = cell.getStringCellValue().trim();
                    if (!value.isEmpty()) {
                        return false;
                    }
                } else {
                    // 如果是其他类型且不为空，则行不为空
                    return false;
                }
            }
        }
        return true;
    }

    /**
     * 解析详细切割方案
     * 
     * @param workbook Excel工作簿
     * @param orderId 订单ID
     * @param orderNo 订单编号
     * @return 详细切割方案列表
     */
    private List<XingeProfileCutDetail> parseDetailMaterials(Workbook workbook, String orderId, String orderNo) {
        List<XingeProfileCutDetail> allCutDetails = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(0);
        
        // 查找所有切割方案区域
        List<CutSchemeRegion> cutSchemeRegions = findCutSchemeRegions(sheet);
        if (cutSchemeRegions.isEmpty()) {
            log.info("未找到切割方案区域");
            return allCutDetails;
        }
        
        log.info("找到 {} 个切割方案区域", cutSchemeRegions.size());
        
        // 先查找第一个切割方案的表头行和列索引，后续所有切割方案都使用这个表头信息
        Map<String, Integer> columnIndexMap;
        if (!cutSchemeRegions.isEmpty()) {
            CutSchemeRegion firstRegion = cutSchemeRegions.get(0);
            int headerRowNum = findHeaderRowInRegion(sheet, firstRegion);
            if (headerRowNum != -1) {
                columnIndexMap = parseCutSchemeColumnIndexes(sheet.getRow(headerRowNum));
                log.info("找到切割方案表头行，列索引: {}", columnIndexMap);
            } else {
                columnIndexMap = null;
            }
        } else {
            columnIndexMap = null;
        }

        if (columnIndexMap == null || columnIndexMap.isEmpty()) {
            log.warn("未找到切割方案表头行或列索引为空");
            return allCutDetails;
        }
        
        // 使用线程池并行处理每个切割方案区域，共享表头信息
        List<Future<List<XingeProfileCutDetail>>> futures = new ArrayList<>();
        for (CutSchemeRegion region : cutSchemeRegions) {
            futures.add(executorService.submit(() -> parseSingleCutScheme(sheet, region, orderId, orderNo, columnIndexMap)));
        }
        
        // 收集所有线程的处理结果
        for (Future<List<XingeProfileCutDetail>> future : futures) {
            try {
                List<XingeProfileCutDetail> cutDetails = future.get();
                allCutDetails.addAll(cutDetails);
            } catch (Exception e) {
                log.error("解析切割方案时发生错误", e);
            }
        }
        
        return allCutDetails;
    }
    
    /**
     * 查找所有切割方案区域
     * 
     * @param sheet Excel工作表
     * @return 切割方案区域列表
     */
    private List<CutSchemeRegion> findCutSchemeRegions(Sheet sheet) {
        List<CutSchemeRegion> regions = new ArrayList<>();
        int startRow = -1;
        
        // 遍历所有行查找切割方案区域的开始行
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;
            
            Cell firstCell = row.getCell(0);
            if (firstCell != null && firstCell.getCellType() == CellType.STRING) {
                String cellValue = firstCell.getStringCellValue().trim();
                
                // 判断是否是切割方案区域的开始行
                if (cellValue.contains("名称:") && cellValue.contains("米重：")) {
                    if (startRow != -1) {
                        // 找到前一个区域的结束行
                        int endRow = findCutSchemeEndRow(sheet, startRow, rowNum - 1);
                        regions.add(new CutSchemeRegion(startRow, endRow));
                    }
                    startRow = rowNum;
                }
            }
        }
        
        // 处理最后一个区域
        if (startRow != -1) {
            int endRow = findCutSchemeEndRow(sheet, startRow, sheet.getLastRowNum());
            regions.add(new CutSchemeRegion(startRow, endRow));
        }
        
        return regions;
    }
    
    /**
     * 查找切割方案区域的结束行
     * 
     * @param sheet Excel工作表
     * @param startRow 开始行
     * @param maxRow 最大行号
     * @return 结束行号
     */
    private int findCutSchemeEndRow(Sheet sheet, int startRow, int maxRow) {
        for (int rowNum = startRow + 1; rowNum <= maxRow; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;

            //循环行每一列查看是否包含  合计
            for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {
                Cell cell = row.getCell(colNum);
                if (cell != null && cell.getCellType() == CellType.STRING) {
                    String cellValue = cell.getStringCellValue().trim();
                    if (cellValue.contains("合计")) {
                        return rowNum;
                    }
                }
            }
        }
        
        // 如果找不到包含"合计"的行，返回最大行号
        return maxRow;
    }
    
    /**
     * 在指定区域内查找表头行
     * 
     * @param sheet Excel工作表
     * @param region 切割方案区域
     * @return 表头行号，如果未找到则返回-1
     */
    private int findHeaderRowInRegion(Sheet sheet, CutSchemeRegion region) {
        for (int rowNum = region.getStartRow() + 1; rowNum <= region.getEndRow(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;
            
            //循环列查询表头行
            for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {
                Cell cell = row.getCell(colNum);
                if (cell != null && cell.getCellType() == CellType.STRING) {
                    String cellValue = cell.getStringCellValue().trim();
                    if (cellValue.equals("序号")) {
                        return rowNum;
                    }
                }
            }
        }
        
        return -1;
    }
    
    /**
     * 解析单个切割方案
     * 
     * @param sheet Excel工作表
     * @param region 切割方案区域
     * @param orderId 订单ID
     * @param orderNo 订单编号
     * @param columnIndexMap 共享的列索引映射
     * @return 切割方案详情列表
     */
    private List<XingeProfileCutDetail> parseSingleCutScheme(Sheet sheet, CutSchemeRegion region, String orderId, String orderNo, Map<String, Integer> columnIndexMap) {
        List<XingeProfileCutDetail> cutDetails = new ArrayList<>();
        
        try {
            // 解析开始行中的型材信息
             Row startRow = sheet.getRow(region.getStartRow());
            if (startRow == null) return cutDetails;
            
            Cell firstCell = startRow.getCell(0);
            if (firstCell == null || firstCell.getCellType() != CellType.STRING) return cutDetails;
            
            String headerValue = firstCell.getStringCellValue().trim();
            String[] headerSplit = headerValue.split("米重：");
            headerValue = headerSplit[0];
            Double meterWeight = Double.valueOf(headerSplit[1]);
            
            // 解析型材编号、名称、颜色和米重
            String profileCode = "";
            String profileName = "";
            String color = "";
            
            // 解析名称、编号和颜色
            int nameIndex = headerValue.indexOf("名称:");
            if (nameIndex >= 0) {
                String nameInfo = headerValue.substring(nameIndex + 3).trim();
                
                // 使用正则表达式解析"YC5501D/边框/木纹/灰色"这样的格式
                Pattern pattern = Pattern.compile("([^/]+)/\\s*([^/]+)(?:/\\s*(.+))?");
                Matcher matcher = pattern.matcher(nameInfo);
                if (matcher.find()) {
                    profileCode = matcher.group(1).trim();
                    profileName = matcher.group(2).trim();
                    color = matcher.groupCount() > 2 && matcher.group(3) != null ? matcher.group(3).trim() : "";
                } else {
                    // 如果正则匹配失败，尝试简单分割
                    String[] parts = nameInfo.split("/");
                    if (parts.length > 0) profileCode = parts[0].trim();
                    if (parts.length > 1) profileName = parts[1].trim();
                    if (parts.length > 2) color = parts[2].trim();
                }
            }
            
            // 根据型材编号查询型材领料表
            String profileMaterialId = findProfileMaterialId(orderId, profileCode);
            
            // 查找数据行起始位置
            int dataStartRow = -1;
            for (int rowNum = region.getStartRow() + 1; rowNum <= region.getEndRow(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;
                
                // 检查是否是序号列的第一个数据行
                Cell serialNoCell = row.getCell(columnIndexMap.get("序号"));
                if (serialNoCell != null) {
                    String serialNoStr = getCellValueAsString(serialNoCell).trim();
                    try {
                        int serialNo = Integer.parseInt(serialNoStr);
                        if (serialNo == 1) {
                            dataStartRow = rowNum;
                            break;
                        }
                    } catch (NumberFormatException e) {
                        // 忽略非数字
                    }
                }
            }
            
            if (dataStartRow == -1) {
                // 如果找不到序号为1的行，就从区域开始行的下一行开始
                dataStartRow = region.getStartRow() + 1;
            }
            
            // 解析数据行
            for (int rowNum = dataStartRow; rowNum < region.getEndRow(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;
                
                // 检查是否是空行
                if (isEmptyRow(row)) continue;
                
                // 检查是否到达合计行
                boolean isEndRow = false;
                for (int colNum = 0; colNum < row.getLastCellNum(); colNum++) {
                    Cell cell = row.getCell(colNum);
                    if (cell != null && cell.getCellType() == CellType.STRING) {
                        String cellValue = cell.getStringCellValue().trim();
                        if (cellValue.contains("合计")) {
                            isEndRow = true;
                            break;
                        }
                    }
                }
                
                if (isEndRow) break;
                
                // 解析行数据
                XingeProfileCutDetail cutDetail = new XingeProfileCutDetail();
                cutDetail.setOrderId(orderId);
                cutDetail.setOrderNo(orderNo);
                cutDetail.setProfileCode(profileCode);
                cutDetail.setProfileName(profileName);
                cutDetail.setColor(color);
                cutDetail.setMeterWeight(meterWeight);
                cutDetail.setProfileMaterialId(profileMaterialId);
                cutDetail.setCreateTime(new Date());
                
                // 序号
                if (columnIndexMap.containsKey("序号")) {
                    String serialNoStr = getCellValueAsString(row.getCell(columnIndexMap.get("序号")));
                    try {
                        cutDetail.setSerialNo(Integer.parseInt(serialNoStr));
                    } catch (NumberFormatException e) {
                        // 忽略非数字序号
                    }
                }
                
                // 长度
                if (columnIndexMap.containsKey("长度")) {
                    Double length = getCellValueAsDouble(row, columnIndexMap.get("长度"));
                    cutDetail.setLength(length);
                }
                
                // 根数
                if (columnIndexMap.containsKey("根数")) {
                    Double quantity = getCellValueAsDouble(row, columnIndexMap.get("根数"));
                    cutDetail.setQuantity(quantity != null ? quantity.intValue() : null);
                }
                
                // 切割方案
                if (columnIndexMap.containsKey("切割方案")) {
                    String cutScheme = getCellValueAsString(row.getCell(columnIndexMap.get("切割方案")));
                    cutDetail.setCutScheme(cutScheme);
                }
                
                // 优化率/余料
                if (columnIndexMap.containsKey("优化率/余料")) {
                    String optimizationRateStr = getCellValueAsString(row.getCell(columnIndexMap.get("优化率/余料")));
                    if (optimizationRateStr.contains("/")) {
                        String[] parts = optimizationRateStr.split("/");
                        if (parts.length > 0) {
                            try {
                                String rateStr = parts[0].trim().replace("%", "");
                                cutDetail.setOptimizationRate(Double.parseDouble(rateStr) / 100);
                            } catch (NumberFormatException e) {
                                // 忽略解析错误
                            }
                        }
                        if (parts.length > 1) {
                            try {
                                cutDetail.setRemnant(Double.parseDouble(parts[1].trim()));
                            } catch (NumberFormatException e) {
                                // 忽略解析错误
                            }
                        }
                    }
                }
                
                cutDetails.add(cutDetail);
            }
            
        } catch (Exception e) {
            log.error("解析单个切割方案时发生错误", e);
        }
        
        return cutDetails;
    }
    
    /**
     * 解析切割方案表头列索引
     * 
     * @param headerRow 表头行
     * @return 列索引映射
     */
    private Map<String, Integer> parseCutSchemeColumnIndexes(Row headerRow) {
        Map<String, Integer> columnMap = new HashMap<>();
        
        if (headerRow == null) return columnMap;
        
        for (int colNum = 0; colNum < headerRow.getLastCellNum(); colNum++) {
            Cell cell = headerRow.getCell(colNum);
            if (cell == null) continue;
            
            String cellValue = getCellValueAsString(cell).trim();
            
            // 根据列标题确定列类型
            if (cellValue.equals("序号")) {
                columnMap.put("序号", colNum);
            } else if (cellValue.equals("长度")) {
                columnMap.put("长度", colNum);
            } else if (cellValue.equals("根数")) {
                columnMap.put("根数", colNum);
            } else if (cellValue.equals("切割方案")) {
                columnMap.put("切割方案", colNum);
            } else if (cellValue.contains("优化率") || cellValue.contains("余料")) {
                columnMap.put("优化率/余料", colNum);
            }
        }
        
        return columnMap;
    }
    
    /**
     * 根据型材编号查询型材领料表ID
     * 
     * @param orderId 订单ID
     * @param profileCode 型材编号
     * @return 型材领料表ID
     */
    private String findProfileMaterialId(String orderId, String profileCode) {
        List<XingeProfileMaterial> materials = xingeProfileMaterialService.getByOrderIdAndProfileCode(orderId, profileCode);
        if (!materials.isEmpty()) {
            return materials.get(0).getId();
        }
        return null;
    }
    
    /**
     * 切割方案区域
     */
    private static class CutSchemeRegion {
        private final int startRow;
        private final int endRow;
        
        public CutSchemeRegion(int startRow, int endRow) {
            this.startRow = startRow;
            this.endRow = endRow;
        }
        
        public int getStartRow() {
            return startRow;
        }
        
        public int getEndRow() {
            return endRow;
        }
    }
} 
